
package eti.vilas.beans;

import java.io.Serializable;
import java.sql.SQLException;

public class Item implements Serializable
{
	
   private String placa;
   private Veiculo veiculo;
   private String combustivel;
   private Integer km;
   private Integer litros;
   private double total;

   
	public String getPlaca() {
		return placa;
	}

	public void setPlaca(String placa) {
		this.placa = placa;
	}

	public Veiculo getVeiculo() {
		return veiculo;
	}

	public void setVeiculo(Veiculo veiculo) {
		this.veiculo = veiculo;
	}

	public String getCombustivel() {
		return combustivel;
	}
   
   public void setCombustivel(String combustivel)
   {
      this.combustivel = combustivel;
   }

   
   public double getTotal()
   {
      return total;
   }

   
   public void setTotal(double total)
   {
      this.total = total;
   }

   public Integer getKm()
   {
      return km;
   }

   public void setKm(Integer km)
   {
      this.km = km;
   }

   public Integer getLitros()
   {
      return litros;
   }

   public void setLitros(Integer litros)
   {
      this.litros = litros;
   }
   
   public double getConsumo()
   {
      return (double)km / (double)litros;
   }
   

   @Override
   public String toString()
   {
      return "Item [combustivel=" + combustivel + ", km=" + km + ", litros=" + litros + ", total=" + total + ", consumo()=" + getConsumo() + "]";
   }


   public static String sqlSelectByCombustivel(String combustivel)
   {
      return String.format("select sum((A.km_end - A.km_start)) kms, sum(A.litros) litros from abastecimento A where A.combustivel='%s'",combustivel);
   }

   public static String sqlSelectGroupByCombustivel()
   {
      return String.format("select A.combustivel combustivel, A.placa, sum(A.vr_total) total, sum((A.km_end - A.km_start)) kms, sum(A.litros) litros from abastecimento A GROUP BY A.combustivel, A.placa ORDER BY A.placa");
   }
   
   public static String sqlSelectGroupByCombustivelByCondutor()
   {
	   return String.format("select A.combustivel Combustivel, A.cnh Condutor, sum(A.vr_total) total, sum((A.km_end - A.km_start)) kms, sum(A.litros) litros from abastecimento A GROUP BY A.condutor, A.cnh");
   }
   
   public static String sqlSelectTotal()
   {
      return String.format("SELECT A.combustivel, sum(A.vr_total) Total FROM abastecimento A GROUP BY A.combustivel");
   }
   
   public static void main(String[] args) throws SQLException, ClassNotFoundException
   {
      DBConnection dbcon = DBConnection.getInstance();
      for (Item i : dbcon.getGroupByComgustivel())
      {
         System.out.println(i);
      }
      dbcon.close();
   }

}
